package com.bothin.experiment4.dao;

import com.bothin.experiment4.dto.ContactDTO;
import com.bothin.experiment4.dto.StatisticDTO;
import com.bothin.experiment4.dto.SumaryDTO;
import com.bothin.experiment4.model.Contact;
import org.apache.ibatis.annotations.Select;
import org.springframework.util.StringUtils;
import tk.mybatis.mapper.common.Mapper;

import java.math.BigDecimal;
import java.util.List;

public interface ContactMapper extends Mapper<Contact> {
    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id " +
            "WHERE contact.contact_id LIKE #{key} OR customer.customer_name LIKE #{key} OR employee.name LIKE #{key}"+
            "ORDER BY contact.create_time DESC")
    public List<ContactDTO> findByKeywordDTO(String key);

    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id " +
            "ORDER BY contact.create_time DESC")
    public List<ContactDTO> findALLDTO();

    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id " +
            "WHERE contact.performance_status = #{status} "+
            "ORDER BY contact.create_time DESC")
    public List<ContactDTO> findAllByPerformanceStatusDTO(Integer status);

    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id\n" +
            "WHERE contact.contact_id = #{contactId} " +
            "ORDER BY contact.create_time DESC")
    public ContactDTO findByIdDTO(String contactId);

    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{employeeId} " +
            "ORDER BY contact.create_time DESC")
    public List<ContactDTO> findByEmployeeIdDTO(Integer employeeId);

    @Select("SELECT contact.contact_id contactId,contact.customer_id customerId,contact.employee_id employeeId,contact.money money,\n" +
            "contact.performance_status performanceStatus,contact.create_time createTime,customer.customer_name customerName,employee.name employeeName\n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "LEFT JOIN employee on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{employeeId} AND contact.contact_id LIKE #{key} OR customer.customer_name LIKE #{key} " +
            "ORDER BY contact.create_time DESC")
    public List<ContactDTO> findByEmployeeIdAndKeywordDTO(Integer employeeId,String key);

    /**
     * 获取一段时间内公司的销售额
     * @param start
     * @param end
     * @return
     */
    @Select("SELECT SUM(contact.money) sum \n" +
            "FROM `contact` \n" +
            "WHERE create_time BETWEEN #{start} AND #{end} ")
    public BigDecimal getTheSumAPeriodOfTime(String start, String end);

    /**
     * 按年统计
     * @return
     */
    @Select("SELECT SUM(money) y,YEAR(create_time) label from `contact` \n" +
            "GROUP BY YEAR(create_time)")
    public List<SumaryDTO> getTheSumEachYear();

    /**
     * 统计某年每个月
     * @param year
     * @return
     */
    @Select("SELECT SUM(money) y,MONTH(create_time) label from `contact` \n" +
            "where YEAR(create_time) = #{year} GROUP BY MONTH(create_time)")
    public List<SumaryDTO> getTheSumEachMouth(String year);

    /**
     * 统计某年每个周
     * @param year
     * @return
     */
    @Select("SELECT SUM(money) y,WEEK(create_time) label from `contact` \n" +
            "where YEAR(create_time) = #{year} GROUP BY WEEK(create_time)")
    public List<SumaryDTO> getTheSumEachWeek(String year);


}